package com.smartwebx.ha.web.admin.module.screen.report;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleCallableStatement;
import com.smartwebx.ha.usebean.Paging;

public class AjaxCommonReportDal {
	public static void main(String[] args) {
		//JPTJ('2012-07-21 06:11:00','2014-08-22 04:12:00')
		Paging p = new Paging();
		p.setKey1("2012-07-21 06:11:00");
		p.setKey2("2014-08-22 04:12:00");
		AjaxCommonReportDal a = new AjaxCommonReportDal();
		a.getData(p, "JSSFK.JPTJ", "2");
	}
	
	public List<CommonReport> getData(Paging page, String call, String number){
		Connection conn = null;
        CallableStatement statement = null;
        ResultSet rs = null;
        int j = 0;
        int m = 0;
        int s = 0;
        List<CommonReport> cList = new ArrayList<CommonReport>();
        CommonReport c = new CommonReport();
        StringBuffer sbTitle = new StringBuffer();
        String sTitle = null;
        StringBuffer sbValues = new StringBuffer();
        String sValues = null;
        StringBuffer sCall = new StringBuffer();
        sCall.append("{?=call ");
        sCall.append(call);
        sCall.append("(");
        for(int n=0 ; n<Integer.parseInt(number) ; n++){
        	sCall.append("?");
        	if(n==Integer.parseInt(number)-1){
        		sCall.append(")}");
        	}else{
        		sCall.append(",");
        	}
        }
        try {
        	ConnUtils connUtils = new ConnUtils();
			conn = connUtils.getConnection();
			statement = conn.prepareCall(sCall.toString());
            statement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
            for(int n=0 ; n<Integer.parseInt(number) ; n++){
            	if(s==n){
                	statement.setString(2, page.getKey1());
                }else if(n-s==1){
                	statement.setString(3, page.getKey2());
                }else if(n-s==2){
                	statement.setString(4, page.getKey3());
                }else if(n-s==3){
                	statement.setString(5, page.getKey4());
                }else if(n-s==4){
                	statement.setString(6, page.getKey5());
                }else if(n-s==5){
                	statement.setString(7, page.getKey6());
                }
            }
            statement.executeUpdate();
            rs = ((OracleCallableStatement) statement).getCursor(1);
           /* System.out.println(rs.next());
            if(rs.next()==false){
            	ConnUtils.free(rs, statement, conn);
            	//JOptionPane.showMessageDialog(null, "参数不匹配或查询结果为空!");
            }else{*/
            	ResultSetMetaData rsmd = null;
                rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
    			while(rs.next()){
    				StringBuffer sbValue = new StringBuffer();
    		        String sValue = null;
    		        if(m==0){
    		        	sbValue.append("[");
    		        	m = 1;
    		        }
    				if(j==0){
    					String columnName = "";
    					String topName = "";
    					String topName2 = "";
    					int istopName = 0;
    					String[] columnNames = null;
    					sbTitle.append("[");
    					sbTitle.append("{\"type\":\"indexcolumn\",\"width\":\"40px\"},");
    					for(int i = 1 ; i<= columnCount ; i++){ 
    						columnName = rsmd.getColumnName(i); 
    						if (columnName.indexOf("|")>=0) {
    							istopName = 1;
    							columnNames = columnName.split("\\|");
    							if (!columnNames[0].equals(topName)) {
    								topName2 = columnNames[0];
    								if (!topName.equals(topName2)&&!"".equals(topName)) {
    									sbTitle.delete(sbTitle.lastIndexOf(","),sbTitle.length()); 
    									sbTitle.append("]}");
    									sbTitle.append(",");
    								}
    								topName = columnNames[0];
        							sbTitle.append("{\"header\":\""+topName);
        							sbTitle.append("\",\"align\":\"center\",\"headerAlign\":\"center\",\"allowSort\":true,\"dataType\":\"string\",\"columns\":[");
								}
    							sbTitle.append("{\"field\":\"col"+i);
    							sbTitle.append("\",\"header\":\"");
    							sbTitle.append(columnNames[1]+"\",\"align\":\"center\",\"headerAlign\":\"center\",\"allowSort\":true,\"dataType\":\"string\",\"width\":\""+(1/(float)columnCount)*100+"%\"},");
							}else {
								if (istopName == 1) {
									sbTitle.delete(sbTitle.lastIndexOf(","),sbTitle.length()); 
									sbTitle.append("]}");
									sbTitle.append(",");
								}
								istopName = 0;
								topName = "";
								sbTitle.append("{\"field\":\"col"+i);
	    						sbTitle.append("\",\"header\":\"");
	    						sbTitle.append(columnName+"\",\"align\":\"center\",\"headerAlign\":\"center\",\"allowSort\":true,\"dataType\":\"string\",\"width\":\""+(1/(float)columnCount)*100+"%\"},");
							}
    					}
    					if (istopName == 1) {
							sbTitle.delete(sbTitle.lastIndexOf(","),sbTitle.length()); 
							sbTitle.append("]}");
							sbTitle.append(",");
						}
    					sTitle = sbTitle.toString();
    					sTitle = sTitle.substring(0, sTitle.length()-1)+"]";
    					j = 1;
    				}
    				
    				sbValue.append("{");
    				for(int i = 1 ; i<= columnCount ; i++){ 
    					sbValue.append("\"col"+i+"\":\"");
    					String columnValue = rs.getString(i);
    					if("null".equals(columnValue)||columnValue==null){
    						columnValue = "";
    					}else if(".".equals(columnValue.substring(0, 1))){
    						columnValue = 0 + columnValue;
    					}else if(columnValue.contains("-.")){
    						columnValue =columnValue.split("-.")[0] + "-0." + columnValue.split("-.")[1];
    					}
    					sbValue.append(columnValue+"\",");
    				} 
    				sValue = sbValue.toString();
    				sValue = sValue.substring(0, sValue.length()-1)+"},";
    				sbValues.append(sValue);
    			}
    			sValues = sbValues.toString();
    			if(sValues.length()>0){
    				sValues = sValues.substring(0, sValues.length()-1)+"]";
    			}
    			ConnUtils.free(rs, statement, conn);
            //}
		} catch (SQLException e) { 
			ConnUtils.free(rs, statement, conn);
			//JOptionPane.showMessageDialog(null, "请检查参数是否填写正确或漏填！");
		}
        c.setsTitle(sTitle);
        c.setsValue(sValues);
        cList.add(c);
		return cList; 
	}
	
	// 根据Unicode编码完美的判断中文汉字和符号
    private static boolean isChinese(char c) {
        Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
        if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B
                || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS
                || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION) {
            return true;
        }
        return false;
    }
	
	// 完整的判断中文汉字和符号
    public static boolean isChinese(String strName) {
        char[] ch = strName.toCharArray();
        for (int i = 0; i < ch.length; i++) {
            char c = ch[i];
            if (isChinese(c)) {
                return true;
            }
        }
        return false;
    }
}
